package speed2010.reformulacao.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;


public class UserDAO {

	private static UserDAO instance;

	static {
		UserDAO.instance = new UserDAO();
	}

	private UserDAO() {
	}

	public static UserDAO getInstance() {
		return UserDAO.instance;
	}

	//Insert Users
	public void insert(User user) {

		Connection con = null;
		PreparedStatement stmt = null;
		String sql = "insert into users (login,name,password,age,studyarea,sex,nationality) values (?,?,?,?,?,?,?)";

		try {
			con = new ConnectionFactory().getConnection();
			stmt = con.prepareStatement(sql);

			stmt.setString(1, user.getLogin());
			stmt.setString(2, user.getName());
			stmt.setString(3, user.getPassword());
			stmt.setInt(4, user.getAge().intValue());
			stmt.setString(5, user.getStudyArea());
			stmt.setString(6, user.getSex().toString());
			stmt.setString(7, user.getNationality());
			stmt.execute();

		} catch (SQLException e) {
			throw new RuntimeException(e);

		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}

	}

	//Get the registered users
	public List<User> getList() {

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		List<User> list = new ArrayList<User>();
		String sql = "select * from users";

		try {
			con = new ConnectionFactory().getConnection();
			stmt = con.prepareStatement(sql);
			rs = stmt.executeQuery();

			while (rs.next()) {
				User u = userFromResultSet(rs);
				list.add(u);
			}

		} catch (SQLException e) {
			throw new RuntimeException(e);

		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}

		return list;
	}

	//Find a specific user
	public User find(String login) {

		Connection con = null;
		PreparedStatement stmt = null;
		ResultSet rs = null;
		User u = null;
		String sql = "select * from users where login = ?";

		try {
			con = new ConnectionFactory().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, login);
			rs = stmt.executeQuery();

			while (rs.next()) {
				u = new User();
				u = userFromResultSet(rs);
			}

		} catch (SQLException e) {
			throw new RuntimeException(e);

		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}

		return u;
	}

	//Remove an user from the database
	public void remove(String login) {

		Connection con = null;
		PreparedStatement stmt = null;
		String sql = "delete from users where login = ?";

		try {
			con = new ConnectionFactory().getConnection();
			stmt = con.prepareStatement(sql);
			stmt.setString(1, login);
			stmt.execute();

		} catch (SQLException e) {
			throw new RuntimeException(e);

		} finally {
			try {
				if (stmt != null)
					stmt.close();
				if (con != null)
					con.close();
			} catch (SQLException e) {
				throw new RuntimeException(e);
			}
		}
	}

	//Get an user after querying the database
	private User userFromResultSet(ResultSet rs) throws SQLException {

		User u = new User();
		u.setId(new Long(rs.getInt("id")));
		u.setLogin(rs.getString("login"));
		u.setName(rs.getString("name"));
		u.setPassword(rs.getString("password"));
		u.setAge(new Integer(rs.getInt("age")));
		u.setStudyArea(rs.getString("studyarea"));
		u.setSex(rs.getString("sex"));
		u.setNationality(rs.getString("nationality"));

		return u;
	}

	public static void main(String[] args) {

		UserDAO dao = new UserDAO();

		
		 
		  User u = new User();
		 
		 u.setName("Gustavo"); u.setAge(new Integer(23));
		  u.setNationality("Brazilian"); u.setLogin("gfd1");
		  u.setPassword("232323"); u.setSex("M"); u.setStudyArea("sdasd");
		 dao.insert(u);
		 

		for (User us : dao.getList()) {
			System.out.println(us.getLogin());
		}

		User u2 = dao.find("gfd1");
		System.out.println("Find:" + u2.getLogin());

	}
}
